/*
 * GDaoImpl.java
 * Created on 16 juin 2007
 *
 * Glacéo Internet Platform
 * http://sourceforge.net/projects/chm/
 *
 * Copyright (c) 2005-2009, Jan and Jens Janke (VirtualHockey Project)
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.hockeo.vhbase.data.dao;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.hockeo.vhbase.data.dao.assoc.GAssociation;
import com.hockeo.vhbase.data.dao.assoc.GNationalAssociation;
import com.hockeo.vhbase.data.dao.club.GClub;
import com.hockeo.vhbase.data.dao.club.GClubHistoryRecord;
import com.hockeo.vhbase.data.dao.club.GClubLeagueHistoryRecord;
import com.hockeo.vhbase.data.dao.club.GClubSeasonSummary;
import com.hockeo.vhbase.data.dao.contest.GChampionsLeague;
import com.hockeo.vhbase.data.dao.contest.GContest;
import com.hockeo.vhbase.data.dao.contest.GContestElement;
import com.hockeo.vhbase.data.dao.contest.GContestHistoryRecord;
import com.hockeo.vhbase.data.dao.contest.GContestRound;
import com.hockeo.vhbase.data.dao.contest.GLeague;
import com.hockeo.vhbase.data.dao.contest.GNationalCup;
import com.hockeo.vhbase.data.dao.contest.GRoundRobinContestElement;
import com.hockeo.vhbase.data.dao.contest.GWorldLeague;
import com.hockeo.vhbase.data.dao.match.GKOMatch;
import com.hockeo.vhbase.data.dao.match.GMatch;
import com.hockeo.vhbase.data.dao.misc.GChart;
import com.hockeo.vhbase.data.dao.misc.GLanguage;
import com.hockeo.vhbase.data.dao.misc.GTranslation;
import com.hockeo.vhbase.data.dao.season.GMatchday;
import com.hockeo.vhbase.data.dao.season.GMoveday;
import com.hockeo.vhbase.data.dao.season.GSeason;
import com.hockeo.vhbase.data.dao.standings.GArchivedStandings;
import com.hockeo.vhbase.data.dao.standings.GECRStandings;
import com.hockeo.vhbase.data.dao.standings.GENRStandings;
import com.hockeo.vhbase.data.dao.standings.GFreeTicket;
import com.hockeo.vhbase.data.dao.standings.GGoalsAccount;
import com.hockeo.vhbase.data.dao.standings.GStandings;
import com.hockeo.vhbase.data.dao.user.GUser;
import com.hockeo.vhbase.data.helper.GArchivedMatchType;
import com.hockeo.vhbase.data.helper.GArchivedStandingsComparator;
import com.hockeo.vhbase.data.helper.GArchivedStandingsType;
import com.hockeo.vhbase.data.helper.GChartType;
import com.hockeo.vhbase.data.helper.GContestHistoryType;
import com.hockeo.vhbase.data.helper.GContestRoundType;
import com.hockeo.vhbase.data.helper.GIntCupParticipant;
import com.hockeo.vhbase.data.helper.GMatchResult;
import com.hockeo.vhbase.data.helper.GMatchType;
import com.hockeo.vhbase.data.helper.GSortDir;
import com.hockeo.vhbase.data.helper.GStandingsRankType;
import com.hockeo.vhbase.data.helper.GStandingsSortCriteria;
import com.hockeo.vhbase.data.helper.GStandingsType;
import com.hockeo.vhbase.error.GDataException;
import com.hockeo.vhbase.utils.data.GHibernateDaoSupport;

/**
 * Default implementation of the {@link IGDao} interface for the Hibernate persistence
 * engine.
 *
 * @version $Id$
 * @author jjanke
 */
@Transactional( readOnly = true )
public class GDaoImpl extends GHibernateDaoSupport implements IGDao
{
  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void makePersistent( Object object )
  {
    getSession().save( object );
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void delete( Object object )
  {
    getSession().delete( object );
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void evict( Object object )
  {
    getSession().evict( object );
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public <T> void deleteAll( Class<T> classEntity )
  {
    Query query = getSession().createQuery( "DELETE FROM " + classEntity.getName() );

    query.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void deleteGoalAccountsMatchesFreeTicketsAndStandings( GContestElement element )
  {
    Session s = getSession();

    // Goal accounts
    //
    Query query = s.createQuery( "delete from GGoalsAccount ga where ga.d_contestElement = :element" );

    query.setEntity( "element", element );
    query.executeUpdate();

    // KO matches
    //
    query = s.createQuery( "delete from GKOMatch m where m.d_contestElement = :element" );

    query.setEntity( "element", element );
    query.executeUpdate();

    // Free tickets
    //
    query = s.createQuery( "delete from GFreeTicket t where t.d_contestRound = :round" );

    query.setEntity( "round", element.getContestRound() );
    query.executeUpdate();

    // Matches
    //
    query = s.createQuery( "DELETE FROM GMatch m WHERE m.d_matchday IN ( FROM GMatchday WHERE d_contestElement = :element )" );

    query.setEntity( "element", element );
    query.executeUpdate();

    // Standings
    //
    query = s.createQuery( "DELETE FROM GStandings s WHERE s.d_matchday IN ( FROM GMatchday WHERE d_contestElement = :element )" );

    query.setEntity( "element", element );
    query.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void deleteStandings( GMoveday moveday )
  {
    SQLQuery sqlquery = getSession().createSQLQuery(
        "DELETE FROM s_standings WHERE matchday_id IN "
            + "( SELECT id FROM s_matchday WHERE first = 0 AND moveday_id = :movedayId )" );

    sqlquery.setInteger( "movedayId", moveday.getId() );
    sqlquery.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void deleteMovedayDependentCharts()
  {
    Query query = getSession().createQuery( "delete from GChart where d_type in (:typeList)" );

    query.setParameterList( "typeList", new GChartType[] { GChartType.RANK_EVOLUTION } );

    query.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void deleteArchivedStandings( final GSeason season, final GContestElement contestElement )
  {
    // Delete all eternal standings and the season specific ones
    //
    Query query = getSession().createQuery(
        "DELETE FROM GArchivedStandings WHERE d_contestElement = :element AND ( ( d_season = :season AND "
            + "d_archiveType = :typeEndOfSeason ) OR d_archiveType = :typeEternal )" );

    query.setEntity( "season", season );
    query.setEntity( "element", contestElement );
    query.setString( "typeEndOfSeason", GArchivedStandingsType.END_OF_SEASON.name() );
    query.setString( "typeEternal", GArchivedStandingsType.ETERNAL.name() );

    query.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void deleteEndOfSeasonData( final GSeason season )
  {
    Session s = getSession();
    Query query;

    // ECR
    //
    query = s.createQuery( "DELETE FROM GECRStandings WHERE d_season = :season" );
    query.setEntity( "season", season );
    query.executeUpdate();

    // ENR
    //
    query = s.createQuery( "DELETE FROM GENRStandings WHERE d_season = :season" );
    query.setEntity( "season", season );
    query.executeUpdate();

    // Club/season summary records
    //
    query = s.createQuery( "DELETE FROM GClubSeasonSummary WHERE d_season = :season" );
    query.setEntity( "season", season );
    query.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void deleteClubHistoryRecords( final GSeason season, final GContest contest )
  {
    Query query = getSession().createQuery(
        "DELETE FROM GClubHistoryRecord WHERE d_contest = :contest AND d_season = :season" );

    query.setEntity( "season", season );
    query.setEntity( "contest", contest );

    query.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void resetClubLeagueAffiliations()
  {
    SQLQuery query = getSession().createSQLQuery(
        "UPDATE m_club c SET c.league_id = ( SELECT h.contest_id FROM h_club_history h WHERE h.contest_type = 'LEAGUE' "
            + "AND h.club_id = c.id ORDER BY h.season_id ASC LIMIT 1 )" );

    query.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void resetClubSeedings( GAssociation assoc )
  {
    SQLQuery sqlquery = getSession().createSQLQuery(
        "UPDATE m_club SET seeding = 0 " + ( assoc != null ? "WHERE association_id = :assocId" : "" ) );

    if ( assoc != null )
      sqlquery.setString( "assocId", assoc.getId() );

    sqlquery.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void placeGoalsToMatch( int nMatchId, int nGoalsHome, int nGoalsAway )
  {
    String strUpdate;
    boolean fHome = false, fAway = false;

    if ( nGoalsHome != -1 && nGoalsAway != -1 )
    {
      strUpdate = "d_nGoalsPlacedHome = :goalsHome, d_nGoalsPlacedAway = :goalsAway ";
      fHome = true;
      fAway = true;
    }
    else if ( nGoalsHome != -1 )
    {
      strUpdate = "d_nGoalsPlacedHome = :goalsHome";
      fHome = true;
    }
    else if ( nGoalsAway != -1 )
    {
      strUpdate = "d_nGoalsPlacedAway = :goalsAway";
      fAway = true;
    }
    else
      return;

    Query query = getSession().createQuery( "UPDATE GMatch SET " + strUpdate + " WHERE d_nId = :id" );

    query.setInteger( "id", nMatchId );

    if ( fHome )
      query.setInteger( "goalsHome", nGoalsHome );

    if ( fAway )
      query.setInteger( "goalsAway", nGoalsAway );

    query.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void resetMatches( GMoveday moveday )
  {
    SQLQuery sqlquery = getSession().createSQLQuery(
        "update s_match set goals_set_home = 0, goals_set_away = 0, played = 0, "
            + "goals_placed_home = 0, goals_placed_away = 0, goals_home = 0, goals_away = 0, "
            + "goals_shootout_home = 0, goals_shootout_away = 0, points_home = 0, points_away = 0, "
            + "cost_home = 0.0, cost_away = 0.0, not_required = 0 "
            + "where matchday_id in ( select id from s_matchday where moveday_id = :movedayId )" );

    sqlquery.setInteger( "movedayId", moveday.getId() );
    sqlquery.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void resetKOMatches( GMoveday moveday )
  {
    SQLQuery sqlquery = getSession().createSQLQuery(
        "UPDATE s_ko_match ko SET club_winner_id = NULL, played_matches = 0, wins_home = 0, wins_away = 0, wins_ot_home = 0, "
            + "wins_ot_away = 0, losses_home = 0, losses_away = 0, losses_ot_home = 0, losses_ot_away = 0, points_home = 0, "
            + "points_away = 0, ecr_points_home = 0.000, ecr_points_away = 0.000, played = 0 "
            + "WHERE exists ( SELECT 'yes' FROM s_match m, s_matchday md WHERE m.matchday_id = md.id "
            + "AND m.ko_match_id = ko.id AND md.moveday_id = :movedayId )" );

    sqlquery.setInteger( "movedayId", moveday.getId() );
    sqlquery.executeUpdate();
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public void archiveAndDeleteMatches( GSeason season )
  {
    // Delete all archived matches that are older than 5 seasons
    //
    Query query = getSession().createQuery( "DELETE FROM GArchivedMatch WHERE d_season.d_nId < :lastKeptSeasonId" );

    query.setInteger( "lastKeptSeasonId", season.getId() - 4 );
    query.executeUpdate();

    // Move matches of the current season to the archive
    //
    SQLQuery sql = getSession().createSQLQuery(
        "INSERT INTO a_match (archive_type, season_id, contest_element_id, round_num, matchday_num, "
            + "club_home_id, club_away_id, goals_home, goals_away, goals_shootout_home, goals_shootout_away, "
            + "points_home, points_away) SELECT IF( m.ko_match_id IS NULL, :archiveTypeRR, :archiveTypeKO ), "
            + ":seasonId, ce.id, cr.num, md.num, club_home_id, club_away_id, goals_home, goals_away, "
            + "goals_shootout_home, goals_shootout_away, points_home, points_away FROM s_match m, s_matchday md, "
            + "m_contest_element ce, m_contest_round cr WHERE m.matchday_id = md.id AND md.contest_element_id = "
            + "ce.id AND ce.contest_round_id = cr.id AND m.not_required = 0" );

    sql.setString( "archiveTypeRR", GArchivedMatchType.ROUND_ROBIN.name() );
    sql.setString( "archiveTypeKO", GArchivedMatchType.KO.name() );
    sql.setInteger( "seasonId", season.getId() );
    sql.executeUpdate();

    // Delete all matches
    //
    deleteAll( GMatch.class );
  }

  @Transactional( readOnly = false, propagation = Propagation.MANDATORY )
  public List<GArchivedStandings> createEternalStandings( GSeason season, GContestElement element )
  {
    // First delete all existing eternal standings
    //
    Query query = getSession().createQuery(
        "DELETE FROM GArchivedStandings WHERE d_contestElement = :element AND d_archiveType = :typeEternal" );

    query.setEntity( "element", element );
    query.setString( "typeEternal", GArchivedStandingsType.ETERNAL.name() );
    query.executeUpdate();

    // Create new eternal standings based on existing end of season standings
    //
    SQLQuery sqlquery = getSession().createSQLQuery(
        "INSERT INTO a_standings (type, archive_type, season_id, club_id, contest_element_id, "
            + "num_seasons, rank_type, matches, points, points_pct, points_avg, wins, wins_ot, losses, losses_ot, "
            + "goals_for, goals_against, goals_diff ) SELECT :standingsType, :archiveTypeEternal, :seasonId, "
            + "club_id, contest_element_id, COUNT(*), :rankType, SUM( matches ), SUM( points ), "
            + "SUM( points ) / ( SUM( matches ) * :pointsVictory ), SUM( points ) / SUM( matches ), "
            + "SUM( wins ), SUM( wins_ot ), SUM( losses ), SUM( losses_ot ), SUM( goals_for ), SUM( goals_against ), "
            + "SUM( goals_for ) - SUM( goals_against ) FROM a_standings WHERE type = :standingsType "
            + "AND archive_type = :archiveTypeSeason AND contest_element_id = :elementId "
            + "GROUP BY contest_element_id, club_id" );

    sqlquery.setString( "standingsType", GStandingsType.TOTAL.name() );
    sqlquery.setString( "archiveTypeEternal", GArchivedStandingsType.ETERNAL.name() );
    sqlquery.setString( "archiveTypeSeason", GArchivedStandingsType.END_OF_SEASON.name() );
    sqlquery.setString( "rankType", GStandingsRankType.MIDFIELD.name() );
    sqlquery.setInteger( "seasonId", season.getId() );
    sqlquery.setInteger( "pointsVictory", GMatchResult.VICTORY.getNumPoints() );
    sqlquery.setString( "elementId", element.getId() );

    sqlquery.executeUpdate();

    // Reassign ranks to newly created eternal standings
    //
    List<GArchivedStandings> list = findArchivedStandings( GArchivedStandingsType.ETERNAL, season, element );
    Collections.sort( list, new GArchivedStandingsComparator( GStandingsSortCriteria.BY_POINTS ) );
    for ( int i = 0; i < list.size(); i++ )
      list.get( i ).setRank( i + 1 );

    return list;
  }

  @SuppressWarnings( "unchecked" )
  public <T> List<T> findAllEntities( Class<T> classEntityType )
  {
    Criteria criteria = getSession().createCriteria( classEntityType );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public <T> T findEntity( Class<T> classEntityType, Serializable entityId )
  {
    return (T) getSession().get( classEntityType, entityId );
  }

  /*
   * @see com.hockeo.vhbase.data.IGlaceoDao#findNumFinishedSeasons()
   */
  public long findNumFinishedSeasons()
  {
    Query query = getSession().createQuery( "select count(*) from GSeason s where s.d_fFinished = true" );

    return ( (Long) query.uniqueResult() ).longValue();
  }

  /*
   * @see com.hockeo.vhbase.data.IGlaceoDao#findCurrentSeason()
   */
  public GSeason findCurrentSeason() throws GDataException
  {
    Criteria criteria = getSession().createCriteria( GSeason.class );

    criteria.add( Restrictions.eq( "d_fActive", true ) );

    try
    {
      GSeason season = (GSeason) criteria.uniqueResult();

      if ( season == null )
        throw new GDataException( "Failed to find current season." );

      return season;
    }
    catch ( HibernateException ex )
    {
      throw new GDataException( "More than one current season found.", ex );
    }
  }

  public GSeason findLastFinishedSeason()
  {
    Integer nLastSeasonId = findLastFinishedSeasonId();

    if ( nLastSeasonId == null )
      return null;

    Criteria criteria = getSession().createCriteria( GSeason.class );
    criteria.add( Restrictions.eq( "d_nId", nLastSeasonId ) );

    return (GSeason) criteria.uniqueResult();
  }

  public Integer findLastFinishedSeasonId()
  {
    Query query = getSession().createQuery( "SELECT max( d_nId ) FROM GSeason s WHERE s.d_fFinished = true" );

    return (Integer) query.uniqueResult();
  }

  @SuppressWarnings( "unchecked" )
  public List<GSeason> findAllSeasons()
  {
    Query query = getSession().createQuery(
        "FROM GSeason WHERE d_nId <= ( SELECT d_nId FROM GSeason WHERE d_fActive = true ) ORDER BY d_nId DESC" );

    return query.list();
  }

  public Map<Integer, GSeason> findAllSeasonsAsMap()
  {
    List<GSeason> list = findAllSeasons();
    Map<Integer, GSeason> map = new LinkedHashMap<Integer, GSeason>( list.size() + 1, 1.0f );

    for ( GSeason s : list )
      map.put( Integer.valueOf( s.getId() ), s );

    return map;
  }

  @SuppressWarnings( "unchecked" )
  public List<GSeason> findSeasonsForClubHistory( GContest contest )
  {
    SQLQuery query = getSession().createSQLQuery(
        "SELECT DISTINCT season_id FROM h_club_history WHERE contest_id = :contestId" );

    query.setString( "contestId", contest.getId() );

    List<Integer> listAvailSeasons = query.list();
    Map<Integer, GSeason> mapSeasons = findAllSeasonsAsMap();

    for ( Integer nSeasonId : listAvailSeasons )
      mapSeasons.get( nSeasonId ).setDataAvailable( true );

    return new ArrayList<GSeason>( mapSeasons.values() );
  }

  @SuppressWarnings( "unchecked" )
  public List<GSeason> findSeasonsForIntRankings()
  {
    // Let's get a list of all finished seasons
    //
    SQLQuery query = getSession().createSQLQuery( "SELECT DISTINCT id FROM h_season WHERE finished = 1" );

    List<Integer> listAvailSeasons = query.list();
    Map<Integer, GSeason> mapSeasons = findAllSeasonsAsMap();

    for ( Integer nSeasonId : listAvailSeasons )
      mapSeasons.get( nSeasonId ).setDataAvailable( true );

    return new ArrayList<GSeason>( mapSeasons.values() );
  }

  @SuppressWarnings( "unchecked" )
  public List<GContest> findContestsForSeason( GSeason season )
  {
    Query query = getSession().createQuery(
        "FROM GContest c WHERE c.d_strId IN ( SELECT l.d_contest.d_strId FROM GContestSeasonLink l "
            + "WHERE l.d_seasonFirst.d_nId <= :seasonId AND "
            + "( l.d_seasonLast IS NULL OR l.d_seasonLast.d_nId >= :seasonId ) )" );

    query.setInteger( "seasonId", season.getId() );

    return query.list();
  }

  /*
   * @see
   * com.hockeo.vhbase.data.IGlaceoDao#findNumMatchdays(com.hockeo.vhbase.data.GContestElement
   * )
   */
  public long findNumMatches( final GContestElement contestElement )
  {
    Query query = getSession().createQuery(
        "select count(*) from GMatch m where m.d_matchday.d_contestElement.d_strId = :elementId" );

    query.setString( "elementId", contestElement.getId() );

    return ( (Long) query.uniqueResult() ).longValue();
  }

  /*
   * @see
   * com.hockeo.vhbase.data.IGlaceoDao#findNumMatchdays(com.hockeo.vhbase.data.GContestElement
   * )
   */
  public long findNumGoalsAccounts( final GContestElement element )
  {
    Query query = getSession().createQuery(
        "select count(*) from GGoalsAccount ga where ga.d_contestElement.d_strId = :elementId" );

    query.setString( "elementId", element.getId() );

    return ( (Long) query.uniqueResult() ).longValue();
  }

  public long findNumClubs()
  {
    Query query = getSession().createQuery( "SELECT count(*) FROM GClub c" );

    return ( (Long) query.uniqueResult() ).longValue();
  }

  public long findNumClubs( final int nCategory )
  {
    Query query = getSession().createQuery( "SELECT count(*) FROM GClub c WHERE c.d_league.d_nCategory = :category" );

    query.setInteger( "category", nCategory );

    return ( (Long) query.uniqueResult() ).longValue();
  }

  public long findNumClubs( final GAssociation assoc )
  {
    Query query = getSession().createQuery( "SELECT count(*) FROM GClub c WHERE c.d_association = :assoc" );

    query.setEntity( "assoc", assoc );

    return ( (Long) query.uniqueResult() ).longValue();
  }

  public long findNumClubs( final GAssociation assoc, final int nCategory )
  {
    Query query = getSession().createQuery(
        "SELECT count(*) FROM GClub c WHERE c.d_league.d_nCategory = :category AND c.d_association = :assoc" );

    query.setInteger( "category", nCategory );
    query.setEntity( "assoc", assoc );

    return ( (Long) query.uniqueResult() ).longValue();
  }

  @SuppressWarnings( "unchecked" )
  public List<GMatch> findMatches( GContestElement element )
  {
    Criteria critMatch = getSession().createCriteria( GMatch.class );
    Criteria critMatchday = critMatch.createCriteria( "d_matchday" );
    critMatchday.add( Restrictions.eq( "d_contestElement", element ) );
    critMatchday.addOrder( Order.asc( "d_nNum" ) );

    return critMatch.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GMatch> findMatches( GMoveday moveday, Class<? extends GUser> classUser )
  {
    Query query = getSession().createQuery(
        "FROM GMatch m WHERE m.d_matchday.d_moveday = :moveday AND " + "( m.d_clubHome.d_user.class = "
            + classUser.getSimpleName() + " OR m.d_clubAway.d_user.class = " + classUser.getSimpleName() + " )" );

    query.setEntity( "moveday", moveday );

    return query.list();
  }

  public Map<Integer, GMatch> findMatchesAsMap( GMoveday moveday, Class<? extends GUser> classUser )
  {
    List<GMatch> list = findMatches( moveday, classUser );
    HashMap<Integer, GMatch> map = new HashMap<Integer, GMatch>( list.size() + 1, 1.0f );

    for ( GMatch m : list )
      map.put( m.getId(), m );

    return map;
  }

  @SuppressWarnings( "unchecked" )
  public List<GMatch> findMatches( GClub club, GMatchType type, GContest contest )
  {
    Criteria criteria = getSession().createCriteria( GMatch.class );

    criteria.createCriteria( "d_matchday" ).addOrder( Order.asc( "d_nNum" ) ).createCriteria( "d_contestElement" ).createCriteria(
        "d_contestRound" ).add( Restrictions.eq( "d_contest", contest ) );

    switch ( type )
    {
      case ALL:
        criteria.add( Restrictions.or( Restrictions.eq( "d_clubHome", club ), Restrictions.eq( "d_clubAway", club ) ) );
        break;

      case AWAY:
        criteria.add( Restrictions.eq( "d_clubAway", club ) );
        break;

      case HOME:
        criteria.add( Restrictions.eq( "d_clubHome", club ) );
        break;

      default:
        throw new IllegalStateException( "Match type " + type.name() + " is not supported." );
    }

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GMatch> findMatches( final GClub club, final GMatchType type )
  {
    Criteria criteria = getSession().createCriteria( GMatch.class );

    switch ( type )
    {
      case ALL:
        criteria.add( Restrictions.or( Restrictions.eq( "d_clubHome", club ), Restrictions.eq( "d_clubAway", club ) ) );
        break;

      case AWAY:
        criteria.add( Restrictions.eq( "d_clubAway", club ) );
        break;

      case HOME:
        criteria.add( Restrictions.eq( "d_clubHome", club ) );
        break;

      default:
        throw new IllegalStateException( "Match type " + type.name() + " is not supported." );
    }

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GMatch> findMatches( GClub club, GContestElement element, GMoveday moveday )
  {
    Criteria criteria = getSession().createCriteria( GMatch.class );
    Criteria subCriteriaMatchday = criteria.createCriteria( "d_matchday" );

    criteria.add( Restrictions.or( Restrictions.eq( "d_clubHome", club ), Restrictions.eq( "d_clubAway", club ) ) );

    subCriteriaMatchday.add( Restrictions.eq( "d_moveday", moveday ) );
    subCriteriaMatchday.add( Restrictions.eq( "d_contestElement", element ) );
    subCriteriaMatchday.addOrder( Order.asc( "d_nNum" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GKOMatch> findAllKOMatches( GContest contest )
  {
    Query query = getSession().createQuery(
        "FROM GKOMatch m WHERE m.d_contestElement.d_contestRound.d_contest = :contest "
            + "ORDER BY m.d_contestElement.d_contestRound.d_nNum DESC" );

    query.setEntity( "contest", contest );

    return query.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GMoveday> findReservedMovedays( GContest contest )
  {
    Criteria criteria = getSession().createCriteria( GMoveday.class );

    if ( contest.instanceOf( GLeague.class ) )
      criteria.add( Restrictions.eq( "d_fReservedLeague", true ) );
    else if ( contest.instanceOf( GNationalCup.class ) )
      criteria.add( Restrictions.eq( "d_fReservedCup", true ) );
    else if ( contest.instanceOf( GChampionsLeague.class ) )
      criteria.add( Restrictions.eq( "d_fReservedEU1", true ) );
    else if ( contest.instanceOf( GWorldLeague.class ) )
      criteria.add( Restrictions.eq( "d_fReservedEU2", true ) );
    else
      throw new IllegalArgumentException( "The contest of type " + contest.getClass().getName() + " is not supported." );

    criteria.addOrder( Order.asc( "d_nId" ) );

    return criteria.list();
  }

  public GMoveday findCurrentMoveday() throws GDataException
  {
    Criteria criteria = getSession().createCriteria( GMoveday.class );
    criteria.add( Restrictions.eq( "d_fActive", true ) );

    try
    {
      // May return null, if there is no active moveday
      //
      return (GMoveday) criteria.uniqueResult();
    }
    catch ( HibernateException ex )
    {
      throw new GDataException( "More than one active moveday found.", ex );
    }
  }

  public GMoveday findFirstNonExecutedMoveday()
  {
    Criteria criteria = getSession().createCriteria( GMoveday.class );
    criteria.add( Restrictions.eq( "d_fExecuted", false ) );
    criteria.addOrder( Order.asc( "d_nId" ) );
    criteria.setMaxResults( 1 );

    return (GMoveday) criteria.uniqueResult();
  }

  public GMoveday findLastPreparedMoveday()
  {
    Criteria criteria = getSession().createCriteria( GMoveday.class );
    criteria.add( Restrictions.eq( "d_fPrepared", true ) );
    criteria.addOrder( Order.desc( "d_nId" ) );
    criteria.setMaxResults( 1 );

    return (GMoveday) criteria.uniqueResult();
  }

  public GMoveday findLastMoveday( GContestElement element )
  {
    Query query = getSession().createQuery(
        "select md.d_moveday from GMatchday md where md.d_fIsLast = true " + "and md.d_contestElement = :element" );

    query.setEntity( "element", element );

    return (GMoveday) query.uniqueResult();
  }

  public boolean areAllMovedaysExecuted()
  {
    Query query = getSession().createQuery( "select count(*) from GMoveday md where md.d_fExecuted = false" );

    return ( (Long) query.uniqueResult() ).longValue() == 0;
  }

  @SuppressWarnings( "unchecked" )
  public List<GMatchday> findMatchdays( GContestElement contestElement )
  {
    Criteria criteria = getSession().createCriteria( GMatchday.class );
    criteria.add( Restrictions.eq( "d_contestElement", contestElement ) );
    criteria.addOrder( Order.asc( "d_nNum" ) );

    return criteria.list();
  }

  public GMatchday findFirstMatchday( final GContestElement contestElement ) throws GDataException
  {
    Criteria criteria = getSession().createCriteria( GMatchday.class );
    criteria.add( Restrictions.eq( "d_contestElement", contestElement ) );
    criteria.add( Restrictions.eq( "d_fIsFirst", true ) );

    GMatchday matchday = (GMatchday) criteria.uniqueResult();

    if ( matchday == null )
      throw new GDataException( "No first matchday found for contest element " + contestElement.getId() );

    return matchday;
  }

  public GMatchday findLastMatchday( final GContestElement contestElement ) throws GDataException
  {
    Criteria criteria = getSession().createCriteria( GMatchday.class );
    criteria.add( Restrictions.eq( "d_contestElement", contestElement ) );
    criteria.add( Restrictions.eq( "d_fIsLast", true ) );

    GMatchday matchday = (GMatchday) criteria.uniqueResult();

    if ( matchday == null )
      throw new GDataException( "No last matchday found for contest element " + contestElement.getId() );

    return matchday;
  }

  /*
   * @see
   * com.hockeo.vhbase.data.IGlaceoDao#findMatchday(com.hockeo.vhbase.data.GContestElement
   * , int)
   */
  public GMatchday findMatchday( GContestElement contestElement, int nMatchdayNum )
  {
    Criteria criteria = getSession().createCriteria( GMatchday.class );
    criteria.add( Restrictions.eq( "d_contestElement", contestElement ) );
    criteria.add( Restrictions.eq( "d_nNum", nMatchdayNum ) );

    GMatchday matchday = (GMatchday) criteria.uniqueResult();

    return matchday;
  }

  /*
   * @seecom.hockeo.vhbase.data.IGlaceoDao#findLastPlayedMatchday(com.hockeo.vhbase.data.
   * GContestElement)
   */
  public GMatchday findLastPlayedMatchday( GContestElement contestElement )
  {
    Criteria criteria = getSession().createCriteria( GMatchday.class );
    criteria.add( Restrictions.eq( "d_contestElement", contestElement ) );
    criteria.add( Restrictions.eq( "d_fAnalysed", true ) );
    criteria.addOrder( Order.desc( "d_nNum" ) );
    criteria.setMaxResults( 1 );

    GMatchday matchday = (GMatchday) criteria.uniqueResult();
    if ( matchday == null )
    {
      try
      {
        matchday = findFirstMatchday( contestElement );
      }
      catch ( GDataException ex )
      {
        return null;
      }
    }

    return matchday;
  }

  public GMatchday findLastMatchdayBeforeMoveday( final GMoveday moveday, final GContestElement contestElement )
  {
    Criteria criteria = getSession().createCriteria( GMatchday.class );
    criteria.add( Restrictions.eq( "d_contestElement", contestElement ) );
    criteria.createCriteria( "d_moveday" ).add( Restrictions.lt( "d_nId", moveday.getId() ) );
    criteria.addOrder( Order.desc( "d_nNum" ) );
    criteria.setMaxResults( 1 );

    return (GMatchday) criteria.uniqueResult();
  }

  @SuppressWarnings( "unchecked" )
  public List<GGoalsAccount> findGoalsAccounts( GContestElement contestElement )
  {
    Criteria criteria = getSession().createCriteria( GGoalsAccount.class );
    criteria.add( Restrictions.eq( "d_contestElement", contestElement ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public Map<GClub, GGoalsAccount> findGoalsAccountsForMatch( GMatch match )
  {
    Criteria criteria = getSession().createCriteria( GGoalsAccount.class );
    criteria.add( Restrictions.eq( "d_contestElement", match.getMatchday().getContestElement() ) );
    criteria.add( Restrictions.or( Restrictions.eq( "d_club", match.getClubHome() ), Restrictions.eq( "d_club",
        match.getClubAway() ) ) );

    final List<GGoalsAccount> list = criteria.list();
    final HashMap<GClub, GGoalsAccount> map = new HashMap<GClub, GGoalsAccount>( list.size() + 1, 1.0f );

    for ( GGoalsAccount ga : list )
      map.put( ga.getClub(), ga );

    return map;
  }

  public Map<GClub, GGoalsAccount> findGoalsAccountsAsMap( GContestElement contestElement )
  {
    final List<GGoalsAccount> list = findGoalsAccounts( contestElement );
    final HashMap<GClub, GGoalsAccount> map = new HashMap<GClub, GGoalsAccount>( list.size() + 1, 1.0f );

    for ( GGoalsAccount ga : list )
      map.put( ga.getClub(), ga );

    return map;
  }

  /*
   * @see com.hockeo.vhbase.data.IGlaceoDao#findLeaguesForAssociation(java.lang.String)
   */
  @SuppressWarnings( "unchecked" )
  public List<GLeague> findLeaguesForAssociation( String strAssociationId )
  {
    Criteria criteria = getSession().createCriteria( GLeague.class );
    criteria.createCriteria( "d_association" ).add( Restrictions.eq( "d_strId", strAssociationId ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GLeague> findLeagues( GAssociation assoc, int nCategory )
  {
    Criteria criteria = getSession().createCriteria( GLeague.class );
    criteria.add( Restrictions.eq( "d_association", assoc ) );
    criteria.add( Restrictions.eq( "d_nCategory", nCategory ) );

    return criteria.list();
  }

  /*
   * @see com.hockeo.vhbase.data.IGlaceoDao#findLeaguesForAssociation(java.lang.String)
   */
  public GNationalCup findCupForAssociation( String strAssociationId ) throws GDataException
  {
    Criteria criteria = getSession().createCriteria( GNationalCup.class );
    criteria.createCriteria( "d_association" ).add( Restrictions.eq( "d_strId", strAssociationId ) );

    try
    {
      return (GNationalCup) criteria.uniqueResult();
    }
    catch ( HibernateException ex )
    {
      throw new GDataException( "More than one cup competition exists for the association '" + strAssociationId + "'." );
    }
  }

  @SuppressWarnings( "unchecked" )
  public List<GLeague> findTopNationalLeagues()
  {
    Query query = getSession().createQuery(
        "SELECT l FROM GLeague l WHERE l.d_association.class = GNationalAssociation AND "
            + "l.d_nCategory = 1 ORDER BY l.d_association.d_strId asc" );

    return query.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClub> findPromotedClubs( final GAssociation assoc, final GSeason season )
  {
    Query query = getSession().createQuery(
        "SELECT r.d_club FROM GClubLeagueHistoryRecord r WHERE r.d_contest.d_association = :assoc "
            + "AND r.d_season = :season AND r.d_fPromoted = true" );

    query.setEntity( "assoc", assoc );
    query.setEntity( "season", season );

    return query.list();
  }

  public Map<Integer, List<GClub>> findPromotedClubsAsMap( final GAssociation assoc, final GSeason season )
  {
    List<GClub> listAllClubs = findPromotedClubs( assoc, season );
    HashMap<Integer, List<GClub>> map = new HashMap<Integer, List<GClub>>( listAllClubs.size() + 1, 1.0f );

    for ( GClub club : listAllClubs )
    {
      List<GClub> listByCategory = map.get( club.getLeague().getCategory() );
      if ( listByCategory == null )
      {
        listByCategory = new ArrayList<GClub>();
        map.put( Integer.valueOf( club.getLeague().getCategory() ), listByCategory );
      }

      listByCategory.add( club );
    }

    return map;
  }

  @SuppressWarnings( "unchecked" )
  public List<GClub> findRelegatedClubs( final GAssociation assoc, final GSeason season )
  {
    Query query = getSession().createQuery(
        "SELECT r.d_club FROM GClubLeagueHistoryRecord r WHERE r.d_contest.d_association = :assoc "
            + "AND r.d_season = :season AND r.d_fRelegated = true" );

    query.setEntity( "assoc", assoc );
    query.setEntity( "season", season );

    return query.list();
  }

  public Map<Integer, List<GClub>> findRelegatedClubsAsMap( final GAssociation assoc, final GSeason season )
  {
    List<GClub> listAllClubs = findRelegatedClubs( assoc, season );
    HashMap<Integer, List<GClub>> map = new HashMap<Integer, List<GClub>>( listAllClubs.size() + 1, 1.0f );

    for ( GClub club : listAllClubs )
    {
      List<GClub> listByCategory = map.get( club.getLeague().getCategory() );
      if ( listByCategory == null )
      {
        listByCategory = new ArrayList<GClub>();
        map.put( Integer.valueOf( club.getLeague().getCategory() ), listByCategory );
      }

      listByCategory.add( club );
    }

    return map;
  }

  @SuppressWarnings( "unchecked" )
  public List<GClub> findClubsForLeagueCategories( GAssociation assoc, int nCatFrom, int nCatTo )
  {
    Criteria criteria = getSession().createCriteria( GClub.class );
    criteria.add( Restrictions.eq( "d_association", assoc ) );
    criteria.createCriteria( "d_league" ).add( Restrictions.between( "d_nCategory", nCatFrom, nCatTo ) );

    return criteria.list();
  }

  public List<GIntCupParticipant> findClubsForWorldLeague( GSeason seasonCurrent )
  {
    List<GIntCupParticipant> listParticipants = new ArrayList<GIntCupParticipant>();
    StringBuilder sbCLParticipants = null;

    // Prepare list of CL participants
    //
    for ( GIntCupParticipant part : findClubsForChampionsLeague( seasonCurrent ) )
    {
      if ( sbCLParticipants == null )
        sbCLParticipants = new StringBuilder();
      else
        sbCLParticipants.append( "," );

      sbCLParticipants.append( "'" ).append( part.getClubId() ).append( "'" );
    }

    String strCLParticipants = sbCLParticipants != null ? sbCLParticipants.toString() : null;

    // Loop over all associations and get the participants
    //
    for ( GNationalAssociation assoc : findAllEntities( GNationalAssociation.class ) )
    {
      GIntCupParticipant partCup = getClubForWLFromCup( seasonCurrent, assoc, strCLParticipants );

      if ( partCup != null )
        listParticipants.add( partCup );

      listParticipants.addAll( getClubsForWLFromLeague( seasonCurrent, assoc, partCup, strCLParticipants ) );
    }

    // Sort list of participants
    //
    Collections.sort( listParticipants );

    // Set seeding
    //
    int nSeeding = 1;
    for ( GIntCupParticipant part : listParticipants )
      part.setSeeding( nSeeding++ );

    return listParticipants;
  }

  /**
   * Returns the club from the specified association that has qualified for the world
   * league through the national cup contest.
   *
   * @param seasonCurrent the current season
   * @param assoc the association from which to look for the qualified club
   * @param strClubsExcludeList the list of clubs already qualified for the Champions
   *          League
   * @return the matching participant
   */
  @SuppressWarnings( "unchecked" )
  private GIntCupParticipant getClubForWLFromCup( final GSeason seasonCurrent, final GNationalAssociation assoc,
      final String strClubsExcludeList )
  {
    SQLQuery query = getSession().createSQLQuery(
        "SELECT h.club_id, s.points_total FROM h_club_history h, m_contest c, h_ecr_standings s "
            + "WHERE h.season_id = :seasonId AND h.contest_id = c.id AND c.association_id = :assocId AND "
            + ( strClubsExcludeList != null ? ( "h.club_id NOT IN (" + strClubsExcludeList + ") AND " ) : "" )
            + "h.rank BETWEEN 1 AND 6 AND h.contest_type = 'CUP' AND s.season_id = h.season_id AND "
            + "s.club_id = h.club_id ORDER BY h.rank ASC LIMIT 1" );

    query.setInteger( "seasonId", seasonCurrent.getId() - 1 );
    query.setString( "assocId", assoc.getId() );

    List<Object[]> listResults = query.list();

    if ( listResults.isEmpty() )
      return null;

    Object[] aobj = listResults.get( 0 );

    return new GIntCupParticipant( (String) aobj[0], 0, ( (BigDecimal) aobj[1] ).doubleValue(), false );
  }

  /**
   * Returns the clubs from the specified association that have qualified for the world
   * league through the national league contest.
   *
   * @param seasonCurrent the current season
   * @param assoc the association from which to look for the qualified clubs
   * @param partCup the participant in the WL determined from the national cup contest
   * @param strClubsExcludeList the list of clubs already qualified for the Champions
   *          League
   * @return the matching participants
   */
  @SuppressWarnings( "unchecked" )
  private List<GIntCupParticipant> getClubsForWLFromLeague( final GSeason seasonCurrent,
      final GNationalAssociation assoc, final GIntCupParticipant partCup, String strClubsExcludeList )
  {
    if ( partCup != null )
      if ( strClubsExcludeList == null )
        strClubsExcludeList = "'" + partCup.getClubId() + "'";
      else
        strClubsExcludeList += ",'" + partCup.getClubId() + "'";

    SQLQuery query = getSession().createSQLQuery(
        "SELECT h.club_id, s.points_total FROM h_club_history h, m_contest c, h_ecr_standings s "
            + "WHERE h.season_id = :seasonId AND h.contest_id = c.id AND c.association_id = :assocId AND "
            + ( strClubsExcludeList != null ? ( "h.club_id NOT IN (" + strClubsExcludeList + ") AND " ) : "" )
            + "h.rank BETWEEN 2 AND 6 AND h.contest_type = 'LEAGUE' AND s.season_id = h.season_id AND "
            + "c.category = 1 AND s.club_id = h.club_id ORDER BY h.rank ASC LIMIT 2" );

    query.setInteger( "seasonId", seasonCurrent.getId() - 1 );
    query.setString( "assocId", assoc.getId() );

    List<Object[]> listResults = query.list();
    List<GIntCupParticipant> listParticipants = new ArrayList<GIntCupParticipant>();

    for ( Object[] aobj : listResults )
      listParticipants.add( new GIntCupParticipant( (String) aobj[0], 0, ( (BigDecimal) aobj[1] ).doubleValue(), false ) );

    return listParticipants;
  }

  @SuppressWarnings( "unchecked" )
  public List<GIntCupParticipant> findClubsForChampionsLeague( GSeason seasonCurrent )
  {
    SQLQuery query = getSession().createSQLQuery(
        "SELECT h.club_id, max( s.points_total ) ecr, "
            + "max( CASE h.contest_type WHEN 'CL' THEN 1 ELSE 0 END ) title_holder from h_club_history h, "
            + "m_contest c, h_ecr_standings s where h.season_id = :seasonId and h.contest_id = c.id and "
            + "( c.category = 1 or c.type in ( 'CL', 'WL' ) ) and h.club_id = s.club_id and s.season_id = :seasonId and "
            + "h.rank = 1 and h.contest_type IN ( 'LEAGUE', 'CL', 'WL' ) group by h.club_id order by 2 DESC;" );

    query.setInteger( "seasonId", seasonCurrent.getId() - 1 );

    List<Object[]> listResults = query.list();
    List<GIntCupParticipant> listParticipants = new ArrayList<GIntCupParticipant>();

    for ( int i = 0; i < listResults.size(); i++ )
    {
      final Object[] aobj = listResults.get( i );

      listParticipants.add( new GIntCupParticipant( (String) aobj[0], i + 1, ( (BigDecimal) aobj[1] ).doubleValue(),
          ( (BigInteger) aobj[2] ).longValue() == 1L ) );
    }

    return listParticipants;
  }

  public long findNumChampionsLeagueParticipants( GSeason seasonCurrent )
  {
    SQLQuery query = getSession().createSQLQuery(
        "SELECT COUNT( DISTINCT h.club_id ) FROM h_club_history h, m_contest c WHERE h.season_id = :seasonId AND "
            + "h.contest_id = c.id AND ( c.category = 1 or c.type in ( 'CL', 'WL' ) ) AND "
            + "h.rank = 1 AND h.contest_type IN ( 'LEAGUE', 'CL', 'WL' );" );

    query.setInteger( "seasonId", seasonCurrent.getId() - 1 );

    return ( (BigInteger) query.uniqueResult() ).longValue();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClub> findClubsNotPlayingInLeagueContest( GAssociation assoc, GSeason season )
  {
    Query query = getSession().createQuery(
        "FROM GClub c WHERE c.d_association = :assoc AND NOT EXISTS ( FROM GArchivedStandings s "
            + "WHERE s.d_season = :season AND s.d_archiveType = :typeSeason AND s.d_type = :standingsType "
            + "AND s.d_contestElement.d_contestRound.d_contest.class = GLeague AND s.d_club = c )" );

    query.setEntity( "assoc", assoc );
    query.setEntity( "season", season );
    query.setString( "typeSeason", GArchivedStandingsType.END_OF_SEASON.name() );
    query.setString( "standingsType", GStandingsType.TOTAL.name() );

    return query.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClub> findClubsInSeedingOrder( GAssociation assoc, GSeason season )
  {
    Query query = getSession().createQuery(
        "SELECT s.d_club FROM GArchivedStandings s WHERE s.d_club.d_association = :assoc AND "
            + "s.d_season = :season AND s.d_archiveType = :typeSeason AND s.d_type = :standingsType AND "
            + "s.d_contestElement.d_contestRound.d_contest.class = GLeague "
            + "ORDER BY s.d_contestElement.d_contestRound.d_contest.d_nCategory ASC, s.d_decPointsPercentage DESC, s.d_nNumMatches DESC, "
            + "s.d_nNumWins DESC, s.d_nNumWinsOT DESC, s.d_nGoalsDiff DESC, s.d_nNumGoalsFor DESC" );

    query.setEntity( "assoc", assoc );
    query.setEntity( "season", season );
    query.setString( "typeSeason", GArchivedStandingsType.END_OF_SEASON.name() );
    query.setString( "standingsType", GStandingsType.TOTAL.name() );

    return query.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClub> findClubsForContestElement( GContestElement contestElement )
  {
    Query query = getSession().createQuery(
        "from GClub c where exists ( select 'yes' from GGoalsAccount g where c.d_strId = g.d_club.d_strId "
            + "and g.d_contestElement.d_strId = :elementId )" );

    query.setString( "elementId", contestElement.getId() );

    return query.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClub> findClubsForLeague( final String strLeagueId )
  {
    Criteria criteria = getSession().createCriteria( GClub.class );
    criteria.add( Restrictions.eq( "d_league.d_strId", strLeagueId ) );
    criteria.addOrder( Order.asc( "d_strName" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClub> findClubsForCupRound( final GSeason season, final GContestRound round, boolean fOrderBySeeding )
  {
    String strOrderBy;

    if ( fOrderBySeeding )
    {
      // as from the 1st round after the main round, teams are seeded according to their
      // results in the previous round. A good criteria is the number of ECR points so far
      // collected.
      //
      if ( round.getRoundType() != GContestRoundType.NOT_DEFINED
          && round.getRoundType().ordinal() < GContestRoundType.ROUND_MAIN.ordinal() )
        strOrderBy = "r.d_decPointsECR DESC";
      else
        strOrderBy = "r.d_nSeeding ASC";
    }
    else
      strOrderBy = "r.d_club.d_strName ASC";

    Query query = getSession().createQuery(
        "SELECT r.d_club FROM GClubAbstractCupHistoryRecord r WHERE r.d_season = :season AND "
            + "r.d_strAssignedRoundId = :roundId ORDER BY " + strOrderBy );

    query.setEntity( "season", season );
    query.setString( "roundId", round.getId() );

    return query.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClubLeagueHistoryRecord> findSpecialClubLeagueHistoryRecords( GSeason season, GNationalAssociation assoc )
  {
    Query query = getSession().createQuery(
        "FROM GClubLeagueHistoryRecord WHERE "
            + "d_season = :season AND d_contest.d_association = :assoc AND ( d_fPromoted = true OR d_fQualifiedEU1 = true OR "
            + "d_fQualifiedEU2 = true OR d_fRelegated = true) ORDER BY d_contest.d_nCategory, d_contest.d_strId, d_nRank" );

    query.setEntity( "season", season );
    query.setEntity( "assoc", assoc );

    return query.list();
  }

  public GContestRound findFirstNonPlayedContestRound( final GContest contest )
  {
    Criteria criteria = getSession().createCriteria( GContestRound.class );
    criteria.add( Restrictions.eq( "d_contest", contest ) );
    criteria.add( Restrictions.eq( "d_fPlayed", false ) );
    criteria.addOrder( Order.asc( "d_nNum" ) );
    criteria.setMaxResults( 1 );

    return (GContestRound) criteria.uniqueResult();
  }

  @SuppressWarnings( "unchecked" )
  public List<GFreeTicket> findFreeTickets( GContestRound round )
  {
    Criteria criteria = getSession().createCriteria( GFreeTicket.class );
    criteria.add( Restrictions.eq( "d_contestRound", round ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GContestElement> findContestElements( GMoveday moveday, GClub club )
  {
    Query query = getSession().createQuery(
        "select distinct m.d_matchday.d_contestElement from GMatch m where m.d_matchday.d_moveday = :moveday and "
            + "( m.d_clubHome = :club or m.d_clubAway = :club )" );

    query.setEntity( "moveday", moveday );
    query.setEntity( "club", club );

    return query.list();
  }

  public GContestRound findCurrentContestRound( final GContest contest )
  {
    GContestRound round = null;

    for ( GContestRound r : contest.getContestRounds() )
    {
      if ( r.isInProgress() )
      {
        round = r;
        break;
      }
      else if ( r.isPlayed() )
        round = r;
    }

    if ( round == null && !contest.getContestRounds().isEmpty() )
      round = contest.getContestRounds().get( 0 );

    return round;
  }

  @SuppressWarnings( "unchecked" )
  public <T extends GClubHistoryRecord> T findClubHistoryRecord( final Class<T> classRecordType,
      final GContest contest, final GSeason season, final GClub club )
  {
    Criteria criteria = getSession().createCriteria( classRecordType );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.add( Restrictions.eq( "d_contest", contest ) );
    criteria.add( Restrictions.eq( "d_club", club ) );

    return (T) criteria.uniqueResult();
  }

  @SuppressWarnings( "unchecked" )
  public <T extends GClubHistoryRecord> T findClubHistoryRecord( final Class<T> classRecordType, final GSeason season,
      final GClub club )
  {
    Criteria criteria = getSession().createCriteria( classRecordType );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.add( Restrictions.eq( "d_club", club ) );

    return (T) criteria.uniqueResult();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClubHistoryRecord> findClubHistoryRecordsForECR( GSeason season, GNationalAssociation assoc )
  {
    Criteria criteria = getSession().createCriteria( GClubHistoryRecord.class );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.createCriteria( "d_club" ).add( Restrictions.eq( "d_association", assoc ) );
    criteria.add( Restrictions.gt( "d_decPointsECR", BigDecimal.ZERO ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public <T extends GClubHistoryRecord> List<T> findClubHistoryRecordByRank( final Class<T> classRecordType,
      final GSeason season, final int nFirstRank, final int nLastRank )
  {
    Criteria criteria = getSession().createCriteria( classRecordType );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.add( Restrictions.between( "d_nRank", nFirstRank, nLastRank ) );
    criteria.addOrder( Order.asc( "d_nRank" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClubHistoryRecord> findClubHistoryRecordsByRank( final GContest contest, final GSeason season,
      final int nFirstRank, final int nLastRank )
  {
    Criteria criteria = getSession().createCriteria( GClubHistoryRecord.class );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.add( Restrictions.eq( "d_contest", contest ) );
    criteria.add( Restrictions.between( "d_nRank", nFirstRank, nLastRank ) );
    criteria.addOrder( Order.asc( "d_nRank" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public <T extends GClubHistoryRecord> Map<GClub, T> findClubHistoryRecordsByContestElement(
      final Class<T> classClubContestHistory, final GSeason season, GContestElement element )
  {
    Query query = getSession().createQuery(
        "FROM " + classClubContestHistory.getName() + " c WHERE c.d_contest = :contest AND c.d_season = :season "
            + "AND EXISTS ( SELECT 'yes' FROM GMatch m WHERE m.d_matchday.d_contestElement = :element "
            + "AND ( m.d_clubHome = c.d_club OR m.d_clubAway = c.d_club ) )" );

    query.setEntity( "season", season );
    query.setEntity( "contest", element.getContest() );
    query.setEntity( "element", element );

    List<T> listCHRs = query.list();
    Map<GClub, T> mapCHRs = new HashMap<GClub, T>( listCHRs.size() + 1, 1.0f );

    for ( T chr : listCHRs )
    {
      if ( !mapCHRs.containsKey( chr.getClub() ) )
        mapCHRs.put( chr.getClub(), chr );
    }

    return mapCHRs;
  }

  @SuppressWarnings( "unchecked" )
  public <T extends GClubHistoryRecord> List<T> findClubHistoryRecords( final Class<T> classRecordType,
      final GNationalAssociation assoc, final GSeason season )
  {
    Criteria criteria = getSession().createCriteria( classRecordType );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.createCriteria( "d_club" ).add( Restrictions.eq( "d_association", assoc ) );

    return criteria.list();
  }

  public <T extends GClubHistoryRecord> Map<GClub, T> findClubHistoryRecordsAsMap( final Class<T> classRecordType,
      final GNationalAssociation assoc, final GSeason season )
  {
    List<T> list = findClubHistoryRecords( classRecordType, assoc, season );
    HashMap<GClub, T> map = new HashMap<GClub, T>( list.size() + 1, 1.0f );

    for ( T chr : list )
      map.put( chr.getClub(), chr );

    return map;
  }

  @SuppressWarnings( "unchecked" )
  public <T extends GClubHistoryRecord> List<T> findClubHistoryRecords( final Class<T> classRecordType,
      final GContest contest, final GSeason season )
  {
    Criteria criteria = getSession().createCriteria( classRecordType );
    criteria.add( Restrictions.eq( "d_contest", contest ) );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.addOrder( Order.asc( "d_nRank" ) );
    criteria.createCriteria( "d_club" ).addOrder( Order.asc( "d_strName" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public <T extends GClubHistoryRecord> List<T> findClubHistoryRecords( final Class<T> classRecordType, final GClub club )
  {
    Criteria criteria = getSession().createCriteria( classRecordType );
    criteria.add( Restrictions.eq( "d_club", club ) );
    criteria.createCriteria( "d_season" ).addOrder( Order.desc( "d_nId" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClubSeasonSummary> findClubSeasonSummaries( final GClub club )
  {
    Criteria criteria = getSession().createCriteria( GClubSeasonSummary.class );
    criteria.add( Restrictions.eq( "d_club", club ) );
    criteria.createCriteria( "d_season" ).addOrder( Order.desc( "d_nId" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GClubSeasonSummary> findClubSeasonSummariesForClubStats()
  {
    Criteria criteria = getSession().createCriteria( GClubSeasonSummary.class );
    criteria.add( Restrictions.or( Restrictions.eq( "d_nLeagueRank", 1 ), Restrictions.or( Restrictions.in(
        "d_leagueRankType", new GStandingsRankType[] { GStandingsRankType.PROMOTED_1, GStandingsRankType.PROMOTED_2,
            GStandingsRankType.RELEGATED } ), Restrictions.or( Restrictions.eq( "d_cupReachedRound",
        GContestRoundType.WINNER ), Restrictions.or( Restrictions.eq( "d_championsLeagueReachedRound",
        GContestRoundType.WINNER ), Restrictions.eq( "d_worldLeagueReachedRound", GContestRoundType.WINNER ) ) ) ) ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GContestHistoryRecord> findContestHistoryRecords( final GContest contest )
  {
    Criteria criteria = getSession().createCriteria( GContestHistoryRecord.class );
    criteria.add( Restrictions.eq( "d_contest", contest ) );
    criteria.createCriteria( "d_season" ).addOrder( Order.desc( "d_nId" ) );

    return criteria.list();
  }

  public GContestHistoryRecord findContestHistoryRecord( final GSeason season, final GContest contest )
      throws GDataException
  {
    Criteria criteria = getSession().createCriteria( GContestHistoryRecord.class );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.add( Restrictions.eq( "d_contest", contest ) );

    try
    {
      // May return null, if there is none
      //
      return (GContestHistoryRecord) criteria.uniqueResult();
    }
    catch ( HibernateException ex )
    {
      throw new GDataException( "More than one contest history record found for contest no. " + contest.getId()
          + " and season " + season.getId(), ex );
    }
  }

  @SuppressWarnings( "unchecked" )
  public List<GContestHistoryRecord> findContestHistoryRecords( final GSeason season, final GContestHistoryType type )
  {
    Criteria criteria = getSession().createCriteria( GContestHistoryRecord.class );
    criteria.add( Restrictions.eq( "d_type", type ) );
    criteria.add( Restrictions.eq( "d_season", season ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GContestHistoryRecord> findMajorContestHistoryRecords( final GSeason season )
  {
    Criteria criteria = getSession().createCriteria( GContestHistoryRecord.class );
    criteria.add( Restrictions.in( "d_type", new Object[] { GContestHistoryType.CHAMPIONS_LEAGUE,
        GContestHistoryType.WORLD_LEAGUE, GContestHistoryType.NATIONAL_CUP, GContestHistoryType.NATIONAL_LEAGUE } ) );
    criteria.add( Restrictions.eq( "d_season", season ) );

    return criteria.list();
  }

  public GENRStandings findENRStanding( final GSeason season, final GNationalAssociation assoc )
  {
    Criteria criteria = getSession().createCriteria( GENRStandings.class );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.add( Restrictions.eq( "d_association", assoc ) );

    return (GENRStandings) criteria.uniqueResult();
  }

  @SuppressWarnings( "unchecked" )
  public List<GENRStandings> findENRStandings( final GSeason season )
  {
    Criteria criteria = getSession().createCriteria( GENRStandings.class );
    criteria.add( Restrictions.eq( "d_season", season ) );

    criteria.addOrder( Order.asc( "d_nRank" ) );

    return criteria.list();
  }

  public GECRStandings findECRStandings( final GClub club )
  {
    Criteria criteria = getSession().createCriteria( GECRStandings.class );
    criteria.add( Restrictions.eq( "d_club", club ) );
    criteria.createCriteria( "d_season" ).addOrder( Order.desc( "d_nId" ) );
    criteria.setMaxResults( 1 );

    return (GECRStandings) criteria.uniqueResult();
  }

  @SuppressWarnings( "unchecked" )
  public List<GECRStandings> findECRStandings( final GSeason season, final GNationalAssociation assoc )
  {
    Criteria criteria = getSession().createCriteria( GECRStandings.class );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.createCriteria( "d_club" ).add( Restrictions.eq( "d_association", assoc ) );

    criteria.addOrder( Order.asc( "d_nRank" ) );

    return criteria.list();
  }

  public Map<GClub, GECRStandings> findECRStandingsAsMap( final GSeason season, final GNationalAssociation assoc )
  {
    List<GECRStandings> list = findECRStandings( season, assoc );
    final HashMap<GClub, GECRStandings> map = new HashMap<GClub, GECRStandings>( list.size() + 1, 1.0f );

    for ( GECRStandings s : list )
      map.put( s.getClub(), s );

    return map;
  }

  @SuppressWarnings( "unchecked" )
  public List<GECRStandings> findECRStandings( final GSeason season, final int nFirst, final int nAmount )
  {
    Criteria criteria = getSession().createCriteria( GECRStandings.class );
    criteria.add( Restrictions.eq( "d_season", season ) );
    criteria.setFirstResult( nFirst );
    criteria.setMaxResults( nAmount );

    criteria.addOrder( Order.asc( "d_nRank" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GECRStandings> findECRStandings( GSeason season, GNationalAssociation assoc, String strSortField,
      GSortDir sortDir, int nOffset, int nLimit )
  {
    Criteria criteria = getSession().createCriteria( GECRStandings.class );
    String strOrderBy = null;

    criteria.setFirstResult( nOffset );
    criteria.setMaxResults( nLimit );

    criteria.add( Restrictions.eq( "d_season", season ) );

    if ( assoc != null )
      criteria.createCriteria( "d_club" ).add( Restrictions.eq( "d_association", assoc ) );

    if ( strSortField == null )
      strSortField = "rank";

    if ( strSortField.equals( "pointsTotal" ) || strSortField.equals( "rank" ) )
      strOrderBy = "d_nRank";
    else if ( strSortField.equals( "pointsS1" ) )
      strOrderBy = "d_decPointsS1";
    else if ( strSortField.equals( "pointsS2" ) )
      strOrderBy = "d_decPointsS2";
    else if ( strSortField.equals( "pointsS3" ) )
      strOrderBy = "d_decPointsS3";
    else if ( strSortField.equals( "pointsS4" ) )
      strOrderBy = "d_decPointsS4";
    else if ( strSortField.equals( "pointsS5" ) )
      strOrderBy = "d_decPointsS5";
    else if ( strSortField.equals( "pointsExpiringNextSeason" ) )
      strOrderBy = "d_decPointsExpiringNextSeason";
    else if ( strSortField.equals( "pointsS1League" ) )
      strOrderBy = "d_decPointsS1League";
    else if ( strSortField.equals( "pointsS1Cup" ) )
      strOrderBy = "d_decPointsS1Cup";
    else if ( strSortField.equals( "pointsS1EU1" ) )
      strOrderBy = "d_decPointsS1EU1";
    else if ( strSortField.equals( "pointsS1EU2" ) )
      strOrderBy = "d_decPointsS1EU2";
    else if ( strSortField.equals( "rankDiff" ) )
      strOrderBy = "d_nRankDiff";

    // ASC is default ordering direction
    //
    if ( sortDir == null || sortDir == GSortDir.DEFAULT )
      sortDir = GSortDir.ASC;

    // order by rank if no other ordering is specified
    //
    if ( strOrderBy == null )
      strOrderBy = "d_nRank";

    if ( sortDir == GSortDir.ASC )
      criteria.addOrder( Order.asc( strOrderBy ) );
    else
      criteria.addOrder( Order.desc( strOrderBy ) );

    return criteria.list();
  }

  public long findNumArchivedStandings( GArchivedStandingsType type, GSeason season, GContestElement element )
  {
    Query query;

    if ( type == GArchivedStandingsType.END_OF_SEASON )
    {
      query = getSession().createQuery(
          "SELECT count(*) FROM GArchivedStandings WHERE d_archiveType = :type AND "
              + "d_season = :season AND d_contestElement = :contestElement" );

      query.setEntity( "season", season );
    }
    else
    {
      query = getSession().createQuery(
          "SELECT count(*) FROM GArchivedStandings WHERE d_archiveType = :type AND "
              + "d_contestElement = :contestElement" );
    }

    query.setString( "type", type.name() );
    query.setEntity( "contestElement", element );

    return ( (Long) query.uniqueResult() ).longValue();
  }

  public long findNumECRRankings( final GSeason season, GNationalAssociation assoc )
  {
    Query query = getSession().createQuery(
        "SELECT count(*) FROM GECRStandings WHERE d_season = :season"
            + ( assoc != null ? " AND d_club.d_association = :assoc" : "" ) );

    query.setEntity( "season", season );

    if ( assoc != null )
      query.setEntity( "assoc", assoc );

    return ( (Long) query.uniqueResult() ).longValue();
  }

  @SuppressWarnings( "unchecked" )
  public List<GArchivedStandings> findArchivedStandings( final GArchivedStandingsType type, final GSeason season,
      final GContestElement contestElement )
  {
    Criteria criteria = getSession().createCriteria( GArchivedStandings.class );

    criteria.add( Restrictions.eq( "d_archiveType", type ) );
    criteria.add( Restrictions.eq( "d_contestElement", contestElement ) );

    if ( type != GArchivedStandingsType.ETERNAL )
      criteria.add( Restrictions.eq( "d_season", season ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GArchivedStandings> findArchivedStandings( final GArchivedStandingsType type, final GSeason season,
      final GContestElement contestElement, final GStandingsType standingsType )
  {
    Criteria criteria = getSession().createCriteria( GArchivedStandings.class );

    criteria.add( Restrictions.eq( "d_archiveType", type ) );
    criteria.add( Restrictions.eq( "d_contestElement", contestElement ) );
    criteria.add( Restrictions.eq( "d_type", standingsType ) );

    if ( type != GArchivedStandingsType.ETERNAL )
      criteria.add( Restrictions.eq( "d_season", season ) );

    return criteria.list();
  }

  public Map<GClub, GArchivedStandings> findArchivedStandingsAsMap( final GArchivedStandingsType type,
      final GSeason season, final GContestElement contestElement )
  {
    List<GArchivedStandings> list = findArchivedStandings( type, season, contestElement );
    Map<GClub, GArchivedStandings> map = new HashMap<GClub, GArchivedStandings>( list.size() + 1, 1.0f );

    for ( GArchivedStandings as : list )
      map.put( as.getClub(), as );

    return map;
  }

  @SuppressWarnings( "unchecked" )
  public List<GArchivedStandings> findEternalStandings( GContestElement element, String strSortField, GSortDir sortDir,
      int nOffset, int nLimit )
  {
    Criteria criteria = getSession().createCriteria( GArchivedStandings.class );
    String strOrderBy = null;

    criteria.setFirstResult( nOffset );
    criteria.setMaxResults( nLimit );

    criteria.add( Restrictions.eq( "d_archiveType", GArchivedStandingsType.ETERNAL ) );
    criteria.add( Restrictions.eq( "d_contestElement", element ) );

    if ( strSortField == null )
      strSortField = "rank";

    // ordering
    //
    if ( strSortField.equals( "numSeasons" ) )
      strOrderBy = "d_nNumSeasons";
    else if ( strSortField.equals( "rank" ) || strSortField.equals( "numPoints" ) )
      strOrderBy = "d_nRank";
    else if ( strSortField.equals( "numMatches" ) )
      strOrderBy = "d_nNumMatches";
    else if ( strSortField.equals( "numGoalsAgainst" ) )
      strOrderBy = "d_nNumGoalsAgainst";
    else if ( strSortField.equals( "numGoalsFor" ) )
      strOrderBy = "d_nNumGoalsFor";
    else if ( strSortField.equals( "goalsDiff" ) )
      strOrderBy = "d_nGoalsDiff";
    else if ( strSortField.equals( "numLosses" ) )
      strOrderBy = "d_nNumLosses";
    else if ( strSortField.equals( "numLossesOT" ) )
      strOrderBy = "d_nNumLossesOT";
    else if ( strSortField.equals( "numWins" ) )
      strOrderBy = "d_nNumWins";
    else if ( strSortField.equals( "numWinsOT" ) )
      strOrderBy = "d_nNumWinsOT";
    else if ( strSortField.equals( "pointsPercentage" ) )
      strOrderBy = "d_decPointsPercentage";
    else if ( !strSortField.equals( "" ) )
      throw new IllegalArgumentException( "Unexpected sort field " + strSortField );

    // ASC is default ordering direction
    //
    if ( sortDir == null || sortDir == GSortDir.DEFAULT )
      sortDir = GSortDir.ASC;

    // order by rank if no other ordering is specified
    //
    if ( strOrderBy == null )
      strOrderBy = "d_nRank";

    if ( sortDir == GSortDir.ASC )
      criteria.addOrder( Order.asc( strOrderBy ) );
    else
      criteria.addOrder( Order.desc( strOrderBy ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GStandings> findStandings( GClub club, GMatchday matchday, GStandingsType type )
  {
    Criteria criteria = getSession().createCriteria( GStandings.class );

    criteria.add( Restrictions.eq( "d_type", type ) );
    criteria.add( Restrictions.eq( "d_club", club ) );
    criteria.createCriteria( "d_matchday" ).add( Restrictions.eq( "d_contestElement", matchday.getContestElement() ) ).add(
        Restrictions.le( "d_nNum", matchday.getNum() ) ).addOrder( Order.asc( "d_nNum" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GStandings> findStandings( GMatchday matchday, GStandingsType type ) throws GDataException
  {
    List<GStandings> listStandings;

    // Check that the matchday belongs to RoundRobin contest element
    //
    if ( !matchday.getContestElement().instanceOf( GRoundRobinContestElement.class ) )
      throw new IllegalArgumentException( "Standings can only be obtained for round robin style contest elements."
          + "\nThe passed matchday with ID " + matchday.getId() + " refers to a contest element" + "\nwith type "
          + matchday.getContestElement().getClass().getName() );

    // Try to load standings for this matchday from the database
    //
    Criteria criteria = getSession().createCriteria( GStandings.class );
    criteria.add( Restrictions.eq( "d_matchday", matchday ) );
    criteria.add( Restrictions.eq( "d_type", type ) );
    listStandings = criteria.list();

    // Check if we have standings for the given matchday
    //
    if ( listStandings.isEmpty() )
    {
      // If not, we try to find standings for last executed matchday
      //
      GMatchday matchdayLastExecuted = findLastPlayedMatchday( matchday.getContestElement() );

      // If there are no standings for the last executed matchday, we have a data
      // inconsistency
      //
      if ( matchdayLastExecuted.getStandings( type ).isEmpty() )
        throw new GDataException( "No standings are attached to the last executed matchday no. "
            + matchdayLastExecuted.getNum() + " of contest element " + matchday.getContestElement().getId() );

      listStandings = new Vector<GStandings>();
      listStandings.addAll( matchdayLastExecuted.getStandings( type ) );
    }

    return listStandings;
  }

  public GStandings findCurrentStandings( GStandingsType type, GClub club, GRoundRobinContestElement element )
      throws GDataException
  {
    GMatchday matchday = findLastPlayedMatchday( element );

    if ( matchday == null )
      throw new GDataException( "No matchday exists for contest element " + element.getId() );

    Criteria criteria = getSession().createCriteria( GStandings.class );

    criteria.add( Restrictions.eq( "d_type", type ) );
    criteria.add( Restrictions.eq( "d_club", club ) );
    criteria.add( Restrictions.eq( "d_matchday", matchday ) );

    try
    {
      return (GStandings) criteria.uniqueResult();
    }
    catch ( HibernateException ex )
    {
      throw new GDataException( "More than one current standings object exist for the club " + club.getId()
          + " element no. " + element.getId() + " type: " + type.name() );
    }
  }

  @SuppressWarnings( "unchecked" )
  public Map<GStandingsType, GStandings> findCurrentStandings( GClub club, GRoundRobinContestElement element )
      throws GDataException
  {
    GMatchday matchday = findLastPlayedMatchday( element );

    if ( matchday == null )
      throw new GDataException( "No matchday exists for contest element " + element.getId() );

    List<GStandings> list;

    Criteria criteria = getSession().createCriteria( GStandings.class );

    criteria.add( Restrictions.eq( "d_club", club ) );
    criteria.add( Restrictions.eq( "d_matchday", matchday ) );

    list = criteria.list();
    assert list.size() == 3 : "There must be exactly 3 standings objects (TOTAL, HOME and AWAY).";

    HashMap<GStandingsType, GStandings> map = new HashMap<GStandingsType, GStandings>( list.size() + 1, 1.0f );

    for ( GStandings standings : list )
      map.put( standings.getType(), standings );

    return map;
  }

  @SuppressWarnings( "unchecked" )
  public List<GStandings> findCurrentStandings( final GStandingsType type, final GRoundRobinContestElement element )
      throws GDataException
  {
    GMatchday matchday = findLastPlayedMatchday( element );

    if ( matchday == null )
      throw new GDataException( "No matchday exists for contest element " + element.getId() );

    Criteria criteria = getSession().createCriteria( GStandings.class );
    criteria.add( Restrictions.eq( "d_matchday", matchday ) );
    criteria.add( Restrictions.eq( "d_type", type ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GStandings> findCurrentStandings( final GRoundRobinContestElement element ) throws GDataException
  {
    GMatchday matchday = findLastPlayedMatchday( element );

    if ( matchday == null )
      throw new GDataException( "No matchday exists for contest element " + element.getId() );

    Criteria criteria = getSession().createCriteria( GStandings.class );

    criteria.add( Restrictions.eq( "d_matchday", matchday ) );
    criteria.addOrder( Order.asc( "d_type" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GStandings> findCurrentStandings( final GStandingsType type, final GContestElement element,
      final int nStartRank, final int nEndRank ) throws GDataException
  {
    GMatchday matchday = findLastPlayedMatchday( element );

    if ( matchday == null )
      throw new GDataException( "No matchday exists for contest element " + element.getId() );

    Criteria criteria = getSession().createCriteria( GStandings.class );

    criteria.add( Restrictions.eq( "d_matchday", matchday ) );
    criteria.add( Restrictions.eq( "d_type", type ) );
    criteria.add( Restrictions.between( "d_nRank", nStartRank, nEndRank ) );
    criteria.addOrder( Order.asc( "d_nRank" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GStandings> findFinalStandings( final GRoundRobinContestElement element ) throws GDataException
  {
    GMatchday matchday = findLastMatchday( element );

    if ( matchday == null )
      throw new GDataException( "The matchday with the number " + element.getNumMatchdays()
          + " does not exist for contest element " + element.getId() );

    Criteria criteria = getSession().createCriteria( GStandings.class );
    criteria.add( Restrictions.eq( "d_matchday", matchday ) );
    criteria.addOrder( Order.desc( "d_type" ) );
    criteria.addOrder( Order.asc( "d_nRank" ) );

    List<GStandings> list = criteria.list();

    if ( list.isEmpty() )
      throw new GDataException( "No final standings could be retrieved for contest element '" + element.getId()
          + "' and matchday ID " + matchday.getId() );

    return list;
  }

  @SuppressWarnings( "unchecked" )
  public List<GStandings> findFinalStandings( final GRoundRobinContestElement element, final GStandingsType type )
      throws GDataException
  {
    GMatchday matchday = findLastMatchday( element );

    if ( matchday == null )
      throw new GDataException( "The matchday with the number " + element.getNumMatchdays()
          + " does not exist for contest element " + element.getId() );

    Criteria criteria = getSession().createCriteria( GStandings.class );
    criteria.add( Restrictions.eq( "d_type", type ) );
    criteria.add( Restrictions.eq( "d_matchday", matchday ) );
    criteria.addOrder( Order.asc( "d_nRank" ) );

    List<GStandings> list = criteria.list();

    if ( list.isEmpty() )
      throw new GDataException( "No final standings could be retrieved for contest element '" + element.getId()
          + "' and matchday ID " + matchday.getId() );

    return list;
  }

  @SuppressWarnings( "unchecked" )
  public List<GStandings> findStandings( final GContestRound round, final GStandingsType type, final int nMatchdayNum,
      final int nStartRank, final int nEndRank ) throws GDataException
  {
    // Get all the final matchdays
    //
    ArrayList<GMatchday> listMatchdays = new ArrayList<GMatchday>();
    for ( GContestElement element : round.getContestElements() )
      listMatchdays.add( findMatchday( element, nMatchdayNum ) );

    // Find standings
    //
    Criteria criteria = getSession().createCriteria( GStandings.class );
    criteria.add( Restrictions.eq( "d_type", type ) );
    criteria.add( Restrictions.in( "d_matchday", listMatchdays ) );
    criteria.add( Restrictions.between( "d_nRank", nStartRank, nEndRank ) );
    criteria.addOrder( Order.asc( "d_nRank" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GStandings> findCurrentStandings( final GContestRound round, final GStandingsType type,
      final int nStartRank, final int nEndRank ) throws GDataException
  {
    // Get all the final matchdays
    //
    ArrayList<GMatchday> listMatchdays = new ArrayList<GMatchday>();
    for ( GContestElement element : round.getContestElements() )
      listMatchdays.add( findLastPlayedMatchday( element ) );

    // Find standings
    //
    Criteria criteria = getSession().createCriteria( GStandings.class );
    criteria.add( Restrictions.eq( "d_type", type ) );
    criteria.add( Restrictions.in( "d_matchday", listMatchdays ) );
    criteria.add( Restrictions.between( "d_nRank", nStartRank, nEndRank ) );
    criteria.addOrder( Order.asc( "d_nRank" ) );

    return criteria.list();
  }

  @SuppressWarnings( "unchecked" )
  public List<GStandings> findFinalStandings( final GContestRound round, final GStandingsType type,
      final int nStartRank, final int nEndRank ) throws GDataException
  {
    // Get all the final matchdays
    //
    ArrayList<GMatchday> listMatchdays = new ArrayList<GMatchday>();
    for ( GContestElement element : round.getContestElements() )
      listMatchdays.add( findLastMatchday( element ) );

    // Find standings
    //
    Criteria criteria = getSession().createCriteria( GStandings.class );
    criteria.add( Restrictions.eq( "d_type", type ) );
    criteria.add( Restrictions.in( "d_matchday", listMatchdays ) );
    criteria.add( Restrictions.between( "d_nRank", nStartRank, nEndRank ) );
    criteria.addOrder( Order.asc( "d_nRank" ) );

    List<GStandings> list = criteria.list();

    if ( list.isEmpty() )
      throw new GDataException( "No final standings could be retrieved for contest round '" + round.getId() );

    return list;
  }

  @SuppressWarnings( "null" )
  public GChart findChart( final GChartType type, final GClub club, final GMatchday matchday ) throws GDataException
  {
    Criteria criteria = getSession().createCriteria( GChart.class );

    criteria.add( Restrictions.eq( "d_type", type ) );

    switch ( type )
    {
      case RANK_EVOLUTION:
      {
        if ( club == null || matchday == null )
          throw new IllegalArgumentException( "Club or matchday argument is null." );

        criteria.add( Restrictions.eq( "d_club", club ) );
        criteria.add( Restrictions.eq( "d_matchday", matchday ) );

        break;
      }

      default:
        throw new IllegalArgumentException( "Chart type " + type.name() + " is not supported." );
    }

    try
    {
      return (GChart) criteria.uniqueResult();
    }
    catch ( HibernateException ex )
    {
      throw new GDataException( "More than one chart exists for the parameters type: " + type.name()
          + ( club != null ? " club: " + club.getId() : "" )
          + ( matchday != null ? " matchday: " + matchday.getId() : "" ) );
    }
  }

  public GLanguage findDefaultLanguage() throws GDataException
  {
    Criteria criteria = getSession().createCriteria( GLanguage.class );

    criteria.add( Restrictions.eq( "d_fDefault", true ) );

    try
    {
      return (GLanguage) criteria.uniqueResult();
    }
    catch ( HibernateException ex )
    {
      throw new GDataException( "More than one language is registered as default!" );
    }
  }

  public GTranslation findTranslation( String strCode, String strLanguage )
  {
    Criteria criteria = getSession().createCriteria( GTranslation.class );

    criteria.add( Restrictions.eq( "d_strCode", strCode ) );
    criteria.createCriteria( "d_language" ).add( Restrictions.eq( "d_strId", strLanguage ) );

    return (GTranslation) criteria.uniqueResult();
  }
}
